using System;
using System.Data;
using System.Data.OleDb;
using System.Xml;
using System.Windows.Forms;

namespace QuanLyTruong.DAL
{
	/// <summary>
	/// Summary description for DBHandler.
	/// </summary>
	public class DBHandler
	{
		// Fields
		private OleDbCommand cmd;
		private OleDbConnection cnn;
		private OleDbDataAdapter dtApdapter;
		private string TenCSDL;

		
		public DBHandler()
		{
			this.TenCSDL = "";
			try
			{
				XmlDocument document = new XmlDocument();
				document.Load(Module.AppPath + @"\Config.xml");
				XmlNode node = document.DocumentElement.SelectSingleNode("cnnString");
				string[] strArray = node.InnerText.Split(new char[] { '\\' });
				this.TenCSDL = strArray[strArray.Length - 1];
				this.cnn = new OleDbConnection(this.GetConnectionString()+ Module.AppPath + node.InnerText);
			}
			catch (Exception exception)
			{
				throw exception;
			}

		}

		public string GetConnectionString()
		{
			string Provider = "Provider = Microsoft.Jet.OleDB.4.0; Data Source=";
			return Provider;
		}

        public string GetFullConnectionString()
        {
            string Provider = this.cnn.ConnectionString;
            return Provider;
        }
		

		public bool Connect()
		{
			try
			{
				this.cnn.Open();
				return true;
			}
			catch (Exception)
			{
				MessageBox.Show("Không tìm thấy file cơ sở dữ liệu của chương trình (QuanLyTruong.mdb)!","Thông báo!");
				OpenFileDialog file = new OpenFileDialog();
				file.FileName = "*.mdb";
				if ( file.ShowDialog() == DialogResult.OK )
				{
					
					XmlDocument document = new XmlDocument();
					document.Load(Module.AppPath + "\\" + "Config.xml");
					XmlNode node = document.DocumentElement.SelectSingleNode("cnnString");
					node.InnerText = file.FileName;
					document.Save(Module.AppPath + "\\" + "Config.xml");
					this.cnn = new OleDbConnection(GetConnectionString() + node.InnerText);
					this.cnn.Open();
					return true;

				}
				else
				{
					Application.Exit();
				}
				return false;
			}
		}

		public bool DeleteTable(string tableName)
		{
			try
			{
				this.cmd = new OleDbCommand("DELETE FROM " + tableName, this.cnn);
				this.cmd.ExecuteNonQuery();
			}
			catch (Exception exception)
			{
				throw exception;
			}
			return true;
		}

		public bool Disconnect()
		{
			try
			{
				this.cnn.Close();
				return true;
			}
			catch (Exception)
			{
				return false;
			}
		}

		public bool ExecuteNoneQuery(string[] cmdStr)
		{
			if (cmdStr.Length > 0)
			{
				this.cmd = this.cnn.CreateCommand();
				OleDbTransaction transaction = this.cnn.BeginTransaction();
				this.cmd.Connection = this.cnn;
				this.cmd.Transaction = transaction;
				int i = 0;
				try
				{
					for (i = 0; i < cmdStr.Length; i++)
					{
						if(cmdStr[i] != null)
						{
							this.cmd.CommandText = cmdStr[i];
							this.cmd.ExecuteNonQuery();
						}
					}
					transaction.Commit();
					return true;
				}
				catch (Exception exception)
				{
					try
					{
						MessageBox.Show("Lỗi xảy ra tại dòng: " + i.ToString() + "\n" + cmdStr[i].ToString());
						transaction.Rollback();
					}
					catch (OleDbException)
					{
					}
					throw exception;
				}
			}
			return true;
		}

		public bool ExecuteNoneQuery(string cmdStr)
		{
			try
			{
				this.cmd = new OleDbCommand(cmdStr, this.cnn);
				this.cmd.ExecuteNonQuery();
			}
			catch (Exception exception)
			{
				throw exception;
			}
			return true;
		}

		public bool ExecuteNoneQuery(string cmdStr, OleDbConnection cnnnew)
		{
			try
			{
				this.cmd = new OleDbCommand(cmdStr, cnnnew);
				this.cmd.ExecuteNonQuery();
			}
			catch (Exception exception)
			{
				MessageBox.Show(exception.Message);
			}
			return true;
		}


		public string GetColumn(string sql)
		{
			string str = "";
			try
			{
				this.cmd = new OleDbCommand(sql, this.cnn);
				str = this.cmd.ExecuteScalar().ToString();
			}
			catch (Exception exception)
			{
				throw exception;
			}
			return ((str == "") ? "0" : str);
		}

		public OleDbDataReader GetDataReader(string sql)
		{
			OleDbDataReader reader;
			try
			{
				this.cmd = new OleDbCommand(sql, this.cnn);
				reader = this.cmd.ExecuteReader();
			}
			catch (Exception exception)
			{
				throw exception;
			}
			return reader;
		}

		public DataRow GetRow(string sql)
		{
			DataSet set;
			try
			{
				this.dtApdapter = new OleDbDataAdapter(sql, this.cnn);
				set = new DataSet();
				this.dtApdapter.Fill(set);
			}
			catch (Exception exception)
			{
				throw exception;
			}
			if (set.Tables[0].Rows.Count > 0)
			{
				return set.Tables[0].Rows[0];
			}
			return null;
		}

		public DataTable GetTable(string tableName)
		{
			DataSet dataSet = new DataSet();
			try
			{
				this.dtApdapter = new OleDbDataAdapter("SELECT * FROM " + tableName, this.cnn);
				this.dtApdapter.Fill(dataSet, tableName);
			}
			catch (Exception exception)
			{
				throw exception;
			}
			return dataSet.Tables[0];
		}

		public DataTable GetTableByQuery(string sql)
		{
			DataTable table;
			try
			{
				this.dtApdapter = new OleDbDataAdapter(sql, this.cnn);
				DataSet dataSet = new DataSet();
				this.dtApdapter.Fill(dataSet);
				table = dataSet.Tables[0];
			}
			catch (Exception exception)
			{
				throw exception;
			}
			return table;
		}
		public DataTable GetTableByQuery(string sql, OleDbConnection cnnnew)
		{
			DataTable table;
			try
			{
				this.dtApdapter = new OleDbDataAdapter(sql, cnnnew);
				DataSet dataSet = new DataSet();
				this.dtApdapter.Fill(dataSet);
				table = dataSet.Tables[0];
			}
			catch (Exception exception)
			{
				throw exception;
			}
			return table;
		}

 

 

		public DataTable GetTableBySP(OleDbCommand cmd)
		{
			DataTable table;
			try
			{
				cmd.Connection	= this.cnn;
				this.dtApdapter = new OleDbDataAdapter(cmd);
				DataSet dataSet = new DataSet();
				this.dtApdapter.Fill(dataSet);
				table = dataSet.Tables[0];
			}
			catch (Exception exception)
			{
				throw exception;
			}
			return table;
		}

		public bool UpdateTable(DataTable dtTable)
		{
			int num = 0;
			this.cmd = this.cnn.CreateCommand();
			OleDbTransaction transaction = this.cnn.BeginTransaction();
			this.cmd.Connection = this.cnn;
			this.cmd.Transaction = transaction;
			try
			{
				foreach (DataRow row in dtTable.Rows)
				{
					string str = string.Empty;
					num++;
					foreach (DataColumn column in dtTable.Columns)
					{
						if (row[column.ColumnName].ToString() != "")
						{
							str = str + "[" + column.ColumnName + "]='" + row[column.ColumnName].ToString() + "', ";
						}
					}
					str = str.Substring(0, str.Length - 2) + " WHERE ID=" + num;
					this.cmd.CommandText = "UPDATE " + dtTable.TableName + " SET " + str;
					this.cmd.ExecuteNonQuery();
				}
				transaction.Commit();
			}
			catch (Exception exception)
			{
				try
				{
					transaction.Rollback();
				}
				catch (OleDbException)
				{
				}
				throw exception;
			}
			return true;
		}

		public bool UpdateTable(string[] cmdStr)
		{
			if (cmdStr.Length > 0)
			{
				this.cmd = this.cnn.CreateCommand();
				OleDbTransaction transaction = this.cnn.BeginTransaction();
				this.cmd.Connection = this.cnn;
				this.cmd.Transaction = transaction;
				try
				{
					for (int i = 0; i < cmdStr.Length; i++)
					{
						this.cmd.CommandText = cmdStr[i];
						this.cmd.ExecuteNonQuery();
					}
					transaction.Commit();
					return true;
				}
				catch (Exception exception)
				{
					try
					{
						transaction.Rollback();
					}
					catch (OleDbException)
					{
					}
					throw exception;
				}
			}
			return true;
		}

	}
}
